import pymysql


# 删除已存在的表并创建表


def create_table():
    db = pymysql.connect(
        host='49.233.39.160', user='user', password='leboAa!#$123',
        port=3306, charset='utf8', db='lebo16')
    cursor = db.cursor()
    cursor.execute('drop table if exists myzstudents;')
    sql = """
    create table myzstudents(
    id int unsigned primary key auto_increment,
    name varchar(20),
    sex varchar(4),
    age int unsigned);
    """
    try:
        cursor.execute(sql)
        print('数据表创建成功！')
    except Exception as e:
        print('数据表创建失败的原因：%s' % e)
    finally:
        cursor.close()
        db.close()


# 插入数据

def insert_data():
    db = pymysql.connect (
        host='49.233.39.160', user='user', password='leboAa!#$123',
        port=3306, charset='utf8', db='lebo16')
    cursor = db.cursor()
    sql = """insert into myzstudents(name,sex,age) values
    ('林平平','男',23),
    ('岳珊珊','女',22),
    ('令冲冲','男',25),
    ('任小盈','女',23),
    ('岳群群','男',56);"""
    try:
        cursor.execute(sql)
        db.commit()
        print('插入数值成功！')
    except Exception as e:
        print('插入值出错，出错原因是：%s' % e)
        db.rollback()
    finally:
        cursor.close()
        db.close()
# create_table()
# insert_data()
